package com.huayue.dao;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Component;

import com.huayue.entity.Employee;
import com.huayue.util.PageHelper;
import com.mysql.DBHelper;

/**
 * 员工
 */
@Component
public class EmployeeDao implements BaseDao<Employee> {

    @Override
    public int insert(Employee entity) {
        int success = DBHelper.executeUpdate(
            "insert into employee (realname, phone, password, sex, address, idnumber, is_status) values (?,?,?,?,?,?,?);",
            entity.getRealname(), entity.getPhone(), entity.getPassword(), entity.getSex(), entity.getAddress(),
            entity.getIdnumber(), entity.getIs_status());
        success += DBHelper.executeUpdate(
            "insert into role_emp(emp_id,role_id) values((select max(id) from employee) ,?)", entity.getRole_id());

        return success;
    }

    @Override
    public int update(Employee entity) {
        int success = DBHelper.executeUpdate(
            "update employee set realname=?, phone=?, password=?, sex=?, birthday=?, address=?, idnumber=?,is_status=? where id=?;",
            entity.getRealname(), entity.getPhone(), entity.getPassword(), entity.getSex(), entity.getBirthday(),
            entity.getAddress(), entity.getIdnumber(), entity.getIs_status(), entity.getId());
        success +=
            DBHelper.executeUpdate("update role_emp set role_id=? where emp_id=?", entity.getRole_id(), entity.getId());
        return success;
    }

    @Override
    public int delete(Employee entity) {
        return DBHelper.executeUpdate("update employee set is_deleted=1 where id=?;", entity.getId());
    }

    @Override
    public List<Map<String, Object>> findAll(Object... obj) {
        return DBHelper.executeQuery("select * from employee where is_deleted=0");
    }

    @Override
    public List<Map<String, Object>> findPage(PageHelper page, Object... obj) {
        // 传进来一个Object对象,将其强转为所需对象
        Employee emp = (Employee)obj[0];
        String sql = "select emp.*,ro.name role_name,dep.name dep_name from employee emp "
            + " left join role_emp re on emp.id=re.emp_id left join role ro on re.role_id=ro.id "
            + " left join department dep on ro.dep_id=dep.id where emp.is_deleted=0 ";
        if (emp.getDep_id() != -1) {
            sql += " and dep.id=" + emp.getDep_id();
        }
        if (emp.getRole_id() != -1) {
            sql += " and re.role_id=" + emp.getRole_id();
        }
        if (emp.getRealname() != null || "".equals(emp.getRealname())) {
            sql += " and emp.realname like '%" + emp.getRealname() + "%'";
        }
        if (emp.getPhone() != null || "".equals(emp.getPhone())) {
            sql += " and emp.phone like '%" + emp.getPhone() + "%'";
        }
        return DBHelper.executeQuery(sql + " limit ?,? ", page.getBegin(), page.getPageSize());
    }

    @Override
    public Map<String, Object> findOne(Employee entity) {
        String sql = "select emp.*,ro.id role_id,ro.name rname from employee emp "
            + " left join role_emp re on emp.id=re.emp_id left join role ro on re.role_id=ro.id "
            + " left join department dep on ro.dep_id=dep.id where emp.is_deleted=0 and emp.id=? ";
        List<Map<String, Object>> list = DBHelper.executeQuery(sql, entity.getId());
        if (list.size() == 0) {
            return null;
        }
        return list.get(0);
    }

    @Override
    public int count(Object... obj) {
        // 传进来一个Object对象,将其强转为所需对象
        Employee emp = (Employee)obj[0];
        String sql = "select count(*) counts from employee emp "
            + " left join role_emp re on emp.id=re.emp_id left join role ro on re.role_id=ro.id "
            + " left join department dep on ro.dep_id=dep.id where emp.is_deleted=0 ";
        if (emp.getDep_id() != -1) {
            sql += " and dep.id=" + emp.getDep_id();
        }
        if (emp.getRole_id() != -1) {
            sql += " and re.role_id=" + emp.getRole_id();
        }
        if (emp.getRealname() != null || "".equals(emp.getRealname())) {
            sql += " and emp.realname like '%" + emp.getRealname() + "%'";
        }
        if (emp.getPhone() != null || "".equals(emp.getPhone())) {
            sql += " and emp.phone like '%" + emp.getPhone() + "%'";
        }
        return Integer.valueOf(DBHelper.executeQuery(sql).get(0).get("counts").toString());
    }

    /**
     * 查询一个用户账户（手机号）是否存在
     */
    public Map<String, Object> findOneByPhone(Employee employee) {
        List<Map<String, Object>> list = DBHelper.executeQuery(
            "select emp.id,emp.realname,emp.phone,emp.is_status,emp.icon,ro.id role_id from employee emp "
                + "left join role_emp re on emp.id=re.emp_id left join role ro on re.role_id=ro.id "
                + "left join department dep on ro.dep_id=dep.id where emp.is_deleted=0 and emp.phone=?",
            employee.getPhone());
        return list.size() > 0 ? list.get(0) : null;
    }

    /**
     * 修改用户头像
     * 
     * @param employee
     * @return
     */
    public int updateIcon(Employee employee) {
        return DBHelper.executeUpdate("update employee emp set icon=? where id=?", employee.getIcon(),
            employee.getId());
    }

    /**
     * 查询所有拥有审核请购单权限的员工
     * 
     * @return
     */
    public List<Map<String, Object>> findCanAuditRequisitionOrder() {
        return DBHelper.executeQuery(
            " select ep.id,ep.realname,rl.name rname from employee ep " + " left join role_emp re on ep.id = re.emp_id"
                + " left join role rl on re.role_id=rl.id" + " left join authority au on au.role_id = rl.id"
                + " where au.is_deleted=0 and rl.is_deleted=0 and ep.is_deleted=0 and ep.is_status=0 and au.menu_id=44"
                + " GROUP BY ep.id");
    }
}
